-- @owner: @qumin20145
-- @date: 2022-06-14
-- @testpoint: 查询时聚集函数参数为序列

--step1:建表并插入数据;expect:成功
drop table if exists t_ustore_query_0056 cascade ;
create table t_ustore_query_0056(id int constraint pk_ustore_query_0056  primary key,c_int int,c_vchar varchar(55) not null,c_vchar2 varchar(55) not null,c_blob blob not null,c_date date)
with (storage_type=ustore) partition by range(id)
(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(300)
);

insert into t_ustore_query_0056 values(1,100,'abc123',lpad('123abc',50,'abc'),lpad('11100561',50,'1100')::blob,to_timestamp(to_char('1800-01-01 10:51:47'),'yyyy-mm-dd hh24:mi:ss'));
create or replace procedure proc_ustore_query_0056(tname varchar,startall int,endall int) as
sqlst varchar(500);
begin
  for i in startall..endall loop
  sqlst := 'insert into ' || tname ||' select id+'||i||',c_int+'||i||',c_vchar||'||i||',c_vchar2||'||i||',c_blob'||',c_date from '||tname|| ' where id=1';
        execute immediate sqlst;
  end loop;
end;
/
call proc_ustore_query_0056('t_ustore_query_0056',1,50);

--step2:创建序列;expect:成功
drop sequence if exists seq_ustore_query_0056;
create sequence seq_ustore_query_0056 start with 1 maxvalue 1000 increment by 1 cycle;

--step3:查询时聚集函数参数为序列;expect:成功
select c_int,sum(seq_ustore_query_0056.nextval) from t_ustore_query_0056 group by c_int,id order by c_int;
select c_int,count(seq_ustore_query_0056.nextval) from t_ustore_query_0056 group by c_int,id order by c_int;

--step4:清理环境; expect:成功
drop table t_ustore_query_0056 cascade;
drop procedure proc_ustore_query_0056;
drop sequence seq_ustore_query_0056;